********************************************************************************
* determine ticketing carriers to use later to only keep regionals
* ticketing carriers

use regressionsdataset, clear
keep tkcarrier year quarter
duplicates drop
sort tkcarrier year quarter
save id_ticketingcarriers, replace

********************************************************************************
********************************************************************************
* potential entrants among TICKET carriers

use regressionsdataset, clear
keep tkcarrier year quarter origin 
duplicates drop
ge potentialorigin=1
reshape wide potentialorigin, i(year quarter origin) j(tkcarrier) string
foreach var of varlist potential* {
		replace `var'=0 if `var'==.
}

sort year quarter origin
save potentialorigin, replace

use regressionsdataset, clear
keep tkcarrier year quarter dest 
duplicates drop
ge potentialdest=1
reshape wide potentialdest, i(year quarter dest) j(tkcarrier) string
foreach var of varlist potential* {
		replace `var'=0 if `var'==.
}

sort year quarter dest
save potentialdest, replace

********************************************************************************
********************************************************************************
********************************************************************************
*Determine number of MARKETS served into/out of each airport for each major for network variable

use regressionsdataset, clear

keep tkcarrier market origin year quarter
duplicates drop

sort year quarter tkcarrier market origin
bysort origin tkcarrier year quarter: gen markets_served_origin_TKT=_N

keep origin year quarter tkcarrier markets_served_origin_TKT
duplicates drop

sort year quarter origin tkcarrier
save markets_origin.dta, replace

use regressionsdataset, clear

keep tkcarrier market dest year quarter
duplicates drop

bysort dest tkcarrier year quarter: gen markets_served_dest_TKT = _N

sort year quarter dest tkcarrier

keep dest year quarter tkcarrier markets_served_dest_TKT
duplicates drop

save markets_dest.dta, replace

***


use regressionsdataset, clear

* merge the network at the airport - number of markets served by a major out of an airport
sort year quarter origin tkcarrier
merge m:1 year origin tkcarrier using markets_origin.dta
drop if _merge ==2 
drop _merge
sort year quarter dest tkcarrier
merge m:1 year dest tkcarrier using markets_dest.dta
drop if _merge ==2 
drop _merge

save temp, replace

keep tkcarrier market year quarter origin markets_served_origin_TKT
duplicates drop
reshape wide markets_served_origin_TKT, i(market year quarter origin) j(tkcarrier) string
foreach var of varlist markets_served_origin_TKT* {
		replace `var'=0 if `var'==.
}
sort market year quarter origin
save reshaped_market_served_origin, replace
clear
use temp
erase temp.dta
keep tkcarrier market year quarter dest markets_served_dest_TKT
duplicates drop
reshape wide markets_served_dest_TKT, i(market year quarter dest) j(tkcarrier) string
foreach var of varlist markets_served_dest_TKT* {
		replace `var'=0 if `var'==.
}
sort market year quarter dest
save reshaped_market_served_dest, replace

********************************************************************************
* number of airports by regional carrier

use regressionsdataset, clear

keep tkcarrier-quarter observation owned_regional1 owned_regional2 owned_regional3

reshape long opcarrier segment owned_regional, i(observation) j(number)
drop if segment == ""

gen origin = substr(segment, 1, 3)

*drop the subsidiary regionals
drop if owned_regional == 1

keep year quarter opcarrier origin
duplicates drop

bysort year quarter opcarrier: gen airportsinUS_REG=_N

drop origin

rename opcarrier tkcarrier
sort tkcarrier year quarter

merge m:1 tkcarrier year quarter using id_ticketingcarriers

drop if _merge==3

drop _merge

rename tkcarrier opcarrier

duplicates drop

save countairports_operating, replace

use countairports_operating
rename opcarrier opcarrier1
rename airportsinUS_REG airportsinUS_REG1
sort year quarter opcarrier1
save countairports_operating1, replace

use countairports_operating
rename opcarrier opcarrier2
rename airportsinUS_REG airportsinUS_REG2
sort year quarter opcarrier2
save countairports_operating2, replace

use countairports_operating
erase countairports_operating.dta
rename opcarrier opcarrier3
rename airportsinUS_REG airportsinUS_REG3
sort year quarter opcarrier3
save countairports_operating3, replace

********************************************************************************
* number of segments served by regionals

use regressionsdataset, clear

keep tkcarrier-quarter observation owned_regional1 owned_regional2 owned_regional3
*drop segment1 segment2 segment3

reshape long opcarrier segment owned_regional, i(observation) j(number)
drop if opcarrier==""
drop observation number 

drop market tkcarrier

duplicates drop

gen str3 origin=substr(segment,1,3)

*drop owned_regionals
drop if owned_regional == 1
drop owned_regional

bysort opcarrier year quarter origin: gen airportnetworkorigin_REG=_N

keep opcarrier year quarter origin airportnetworkorigin_REG

duplicates drop

rename opcarrier tkcarrier

sort tkcarrier year quarter

merge m:1 tkcarrier year quarter using id_ticketingcarriers
erase id_ticketingcarriers.dta

drop if _merge==3

drop _merge

rename tkcarrier opcarrier
duplicates drop

save countairports_operating_reg, replace



use countairports_operating_reg
rename opcarrier opcarrier1
rename origin origin1
rename airportnetworkorigin_REG airportnetworkorigin_REG1
sort year quarter opcarrier1 origin1
save countairports_operating1_reg, replace
rename origin1 dest1
rename airportnetworkorigin_REG1 airportnetworkdest_REG1
sort year quarter opcarrier1 dest1
save countairports_operating1_reg_dest, replace

use countairports_operating_reg
rename opcarrier opcarrier2
rename origin origin2
rename airportnetworkorigin_REG airportnetworkorigin_REG2
sort year quarter opcarrier2 origin2
save countairports_operating2_reg, replace
rename origin2 dest2
rename airportnetworkorigin_REG2 airportnetworkdest_REG2
sort year quarter opcarrier2 dest2
save countairports_operating2_reg_dest, replace

use countairports_operating_reg
erase countairports_operating_reg.dta
rename opcarrier opcarrier3 
rename origin origin3
rename airportnetworkorigin_REG airportnetworkorigin_REG3
sort year quarter opcarrier3 origin3
save countairports_operating3_reg, replace
rename origin3 dest3
rename airportnetworkorigin_REG3 airportnetworkdest_REG3
sort year quarter opcarrier3 dest3
save countairports_operating3_reg_dest, replace

********************************************************************************
* weather variables
use weekly_weather_data, clear

ge quarter=1 if month==1 | month==2 | month==3
replace quarter=2 if month==4 | month==5 | month==6
replace quarter=3 if month==7 | month==8 | month==9
replace quarter=4 if month==10 | month==11 | month==12

drop month day week date
drop wsf2-wt16

*replace missing snow variables
replace snow = 0 if missing(snow)
replace snwd = 0 if missing(snwd)

foreach var of varlist prcp snow snwd tmax tmin {
       bysort airport year quarter: egen mean`var'=mean(`var')
	   drop `var'
	   rename mean`var' `var'
}

duplicates drop

rename airport origin
foreach var of varlist prcp snow snwd tmax tmin {
	   rename `var' origin`var'
}

sort origin year quarter
save originweather, replace

foreach i of numlist 1(1)3 {
	preserve
	rename origin origin`i'
	rename originprcp origin`i'prcp
	rename originsnow origin`i'snow
	rename originsnwd origin`i'snwd
	rename origintmax origin`i'tmax
	rename origintmin origin`i'tmin
	save origin`i'weather, replace
	restore
}

rename origin dest

local weather "prcp snow snwd tmax tmin"
foreach x of local weather {
	   rename origin`x' dest`x'
}
sort dest year quarter
save destweather, replace

foreach i of numlist 1(1)3 {
	preserve
	rename dest dest`i'
	rename destprcp dest`i'prcp
	rename destsnow dest`i'snow
	rename destsnwd dest`i'snwd
	rename desttmax dest`i'tmax
	rename desttmin dest`i'tmin
	save dest`i'weather, replace
	restore
}

********************************************************************************
* merge all together

use regressionsdataset, clear

sort year quarter origin
merge year quarter origin using potentialorigin
erase potentialorigin.dta
drop if _merge==2
drop _merge

sort year quarter dest
merge year quarter dest using potentialdest
erase potentialdest.dta
drop if _merge==2
drop _merge

local airlines "AA AS B6 CO DL F9 FL HP NK NW TW TZ UA US WN YX"
foreach x of local airlines {
     egen potentialentrant`x'=rmin(potentialorigin`x' potentialdest`x')
	 drop potentialorigin`x' potentialdest`x'
}



sort market year quarter origin
merge market year quarter origin using reshaped_market_served_origin
erase reshaped_market_served_origin.dta
drop if _merge==2
drop _merge
sort market year quarter dest
merge market year quarter dest using reshaped_market_served_dest
erase reshaped_market_served_dest.dta
drop if _merge==2
drop _merge




sort year quarter origin tkcarrier
merge year quarter origin tkcarrier using markets_origin
erase markets_origin.dta
drop if _merge==2
drop _merge

sort year quarter dest tkcarrier
merge year quarter dest tkcarrier using markets_dest
erase markets_dest.dta
drop if _merge==2
drop _merge

sort year quarter opcarrier1 
merge m:1 year quarter opcarrier1 using countairports_operating1
erase countairports_operating1.dta
drop if _merge==2
drop _merge
sort year quarter opcarrier2 
merge  m:1 year quarter opcarrier2 using countairports_operating2
erase countairports_operating2.dta
drop if _merge==2
drop _merge
sort year quarter opcarrier3 
merge  m:1 year quarter opcarrier3 using countairports_operating3
erase countairports_operating3.dta
drop if _merge==2
drop _merge



***
gen str3 origin1=substr(segment1,1,3)
sort year quarter opcarrier1 origin1
merge m:1 year quarter opcarrier1 origin1 using countairports_operating1_reg
erase countairports_operating1_reg.dta
drop if _merge==2
drop _merge

gen str3 origin2=substr(segment2,1,3)
sort year quarter opcarrier2 origin2
merge  m:1 year quarter opcarrier2 origin2 using countairports_operating2_reg
erase countairports_operating2_reg.dta
drop if _merge==2
drop _merge

gen str3 origin3=substr(segment3,1,3)
sort year quarter opcarrier3 origin3
merge m:1  year quarter opcarrier3 origin3 using countairports_operating3_reg
erase countairports_operating3_reg.dta
drop if _merge==2
drop _merge

gen str3 dest1=substr(segment1,4,6)
sort year quarter opcarrier1 dest1
merge  m:1 year quarter opcarrier1 dest1 using countairports_operating1_reg_dest
erase countairports_operating1_reg_dest.dta
drop if _merge==2
drop _merge

gen str3 dest2=substr(segment2,4,6)
sort year quarter opcarrier2 dest2
merge m:1  year quarter opcarrier2 dest2 using countairports_operating2_reg_dest
erase countairports_operating2_reg_dest.dta
drop if _merge==2
drop _merge

gen str3 dest3=substr(segment3,4,6)
sort year quarter opcarrier3 dest3
merge m:1  year quarter opcarrier3 dest3 using countairports_operating3_reg_dest
erase countairports_operating3_reg_dest.dta
drop if _merge==2
drop _merge


sort origin1  year  quarter
merge m:1 origin1  year  quarter using origin1weather
erase origin1weather.dta
drop if _merge==2
drop _merge

sort origin2  year  quarter
merge m:1 origin2  year  quarter using origin2weather
erase origin2weather.dta
drop if _merge==2
drop _merge

sort origin3  year  quarter
merge origin3  year  quarter using origin3weather
erase origin3weather.dta
drop if _merge==2
drop _merge

sort dest1 year quarter
merge m:1 dest1 year quarter using dest1weather
erase dest1weather.dta
drop if _merge == 2
drop _merge

sort dest2 year quarter
merge m:1 dest2 year quarter using dest2weather
erase dest2weather.dta
drop if _merge == 2
drop _merge

sort dest3 year quarter
merge m:1 dest3 year quarter using dest3weather
erase dest3weather.dta
drop if _merge == 2
drop _merge

sort origin year quarter
merge m:1 origin year quarter using originweather
erase originweather.dta
drop if _merge == 2
drop _merge

sort dest  year  quarter
merge m:1 dest  year  quarter using destweather
erase destweather.dta
drop if _merge==2
drop _merge

egen worstprcp=rmax(originprcp destprcp origin1prcp origin2prcp origin3prcp dest1prcp dest2prcp dest3prcp)
drop originprcp destprcp origin1prcp origin2prcp origin3prcp dest1prcp dest2prcp dest3prcp
egen worstsnow=rmax(originsnow destsnow origin1snow origin2snow origin3snow dest1snow dest2snow dest3snow)
drop originsnow destsnow origin1snow origin2snow origin3snow dest1snow dest2snow dest3snow
egen worstsnwd=rmax(originsnwd destsnwd origin1snwd origin2snwd origin3snwd dest1snwd dest2snwd dest3snwd)
drop originsnwd destsnwd origin1snwd origin2snwd origin3snwd dest1snwd dest2snwd dest3snwd
egen worsttmin=rmin(origintmin desttmin origin1tmin origin2tmin origin3tmin dest1tmin dest2tmin dest3tmin)
drop origintmin desttmin origin1tmin origin2tmin origin3tmin dest1tmin dest2tmin dest3tmin
drop origintmax desttmax origin1tmax origin2tmax origin3tmax dest1tmax dest2tmax dest3tmax

save regressionsdatasetwithIVs, replace


